#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import psycopg2
import json

def connect_db():
    """连接数据库"""
    try:
        conn = psycopg2.connect(
            host='localhost',
            database='postgres',
            user='lijun',
            password='kuaile9hao',
            port=5432
        )
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        return None

def query_music_stats():
    """查询音乐统计数据"""
    conn = connect_db()
    if not conn:
        return
        
    try:
        cursor = conn.cursor()
        
        # 查询总体统计
        cursor.execute("SELECT COUNT(*) FROM music")
        music_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM music_download_url")
        download_url_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM music_raw_data")
        raw_data_count = cursor.fetchone()[0]
        
        print("=" * 50)
        print("音乐数据库统计信息")
        print("=" * 50)
        print(f"音乐总数: {music_count}")
        print(f"下载链接总数: {download_url_count}")
        print(f"原始数据记录数: {raw_data_count}")
        print()
        
        # 查询艺术家统计
        cursor.execute("""
            SELECT artist, COUNT(*) as song_count 
            FROM music 
            GROUP BY artist 
            ORDER BY song_count DESC 
            LIMIT 10
        """)
        artist_stats = cursor.fetchall()
        print("歌曲数量最多的前10位艺术家:")
        for i, (artist, count) in enumerate(artist_stats, 1):
            print(f"  {i}. {artist} - {count} 首歌曲")
        print()
        
        # 查询下载链接类型统计
        cursor.execute("""
            SELECT url_type, COUNT(*) as url_count 
            FROM music_download_url 
            GROUP BY url_type 
            ORDER BY url_count DESC
        """)
        url_type_stats = cursor.fetchall()
        print("下载链接类型统计:")
        for url_type, count in url_type_stats:
            print(f"  {url_type} - {count} 个链接")
        print()
        
        # 查询最新添加的5首歌曲
        cursor.execute("""
            SELECT name, artist, created_at 
            FROM music 
            ORDER BY created_at DESC 
            LIMIT 5
        """)
        latest_songs = cursor.fetchall()
        print("最新添加的5首歌曲:")
        for name, artist, created_at in latest_songs:
            print(f"  {name} - {artist} (添加时间: {created_at})")
        print()
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"查询统计数据时出错: {e}")
        if conn:
            conn.close()

def search_music(keyword):
    """根据关键词搜索音乐"""
    conn = connect_db()
    if not conn:
        return
        
    try:
        cursor = conn.cursor()
        
        # 搜索歌曲名或艺术家包含关键词的音乐
        cursor.execute("""
            SELECT id, rid, name, artist, pic 
            FROM music 
            WHERE name ILIKE %s OR artist ILIKE %s
            ORDER BY name
            LIMIT 20
        """, (f"%{keyword}%", f"%{keyword}%"))
        
        results = cursor.fetchall()
        
        print(f"搜索关键词 '{keyword}' 的结果:")
        print("-" * 50)
        if results:
            for i, (id, rid, name, artist, pic) in enumerate(results, 1):
                print(f"{i}. {name} - {artist}")
                print(f"   ID: {id}, RID: {rid}")
                if pic:
                    print(f"   封面: {pic}")
                print()
        else:
            print("未找到匹配的歌曲")
            
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"搜索音乐时出错: {e}")
        if conn:
            conn.close()

def get_download_urls(music_id):
    """获取指定音乐的所有下载链接"""
    conn = connect_db()
    if not conn:
        return
        
    try:
        cursor = conn.cursor()
        
        # 获取音乐基本信息
        cursor.execute("""
            SELECT name, artist 
            FROM music 
            WHERE id = %s
        """, (music_id,))
        
        music_info = cursor.fetchone()
        if not music_info:
            print(f"未找到ID为 {music_id} 的音乐")
            cursor.close()
            conn.close()
            return
            
        name, artist = music_info
        print(f"歌曲: {name} - {artist}")
        print("=" * 50)
        
        # 获取下载链接
        cursor.execute("""
            SELECT url_type, url_value, formatted_url 
            FROM music_download_url 
            WHERE music_id = %s
            ORDER BY url_type
        """, (music_id,))
        
        urls = cursor.fetchall()
        if urls:
            print("下载链接:")
            for url_type, url_value, formatted_url in urls:
                print(f"  {url_type}: {url_value}")
        else:
            print("该歌曲暂无下载链接")
            
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"获取下载链接时出错: {e}")
        if conn:
            conn.close()

def main():
    print("音乐数据库查询工具")
    print("=" * 50)
    
    while True:
        print("\n请选择操作:")
        print("1. 查看数据库统计信息")
        print("2. 搜索音乐")
        print("3. 查看指定音乐的下载链接")
        print("4. 退出")
        
        choice = input("\n请输入选项 (1-4): ").strip()
        
        if choice == '1':
            query_music_stats()
        elif choice == '2':
            keyword = input("请输入搜索关键词: ").strip()
            if keyword:
                search_music(keyword)
            else:
                print("关键词不能为空")
        elif choice == '3':
            try:
                music_id = int(input("请输入音乐ID: ").strip())
                get_download_urls(music_id)
            except ValueError:
                print("请输入有效的数字ID")
        elif choice == '4':
            print("再见!")
            break
        else:
            print("无效选项，请重新输入")

if __name__ == "__main__":
    main()